package net.tycmc.cty.ctyms.timejob.iems.dao;
import java.util.List;
import java.util.Map;

import com.tycmc.functions.util.MapGetter;


import net.tycmc.bulb.common.dao.IBaseDAO;
import net.tycmc.bulb.common.dao.ITransactionDAO;
import net.tycmc.bulb.dbutil.global.GlobalDBVar;
import net.tycmc.cty.ctyms.timejob.common.DateRegUtil;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSDAO;

public class AllScanDao {
	private IBaseDAO baseDAO;
	private ITransactionDAO tranDAO;
	public ITransactionDAO getTranDAO() {
		return tranDAO;
	}

	public void setTranDAO(ITransactionDAO tranDAO) {
		this.tranDAO = tranDAO;
	}

	/**
	 * @return the baseDAO
	 */
	public IBaseDAO getBaseDAO() {
		return baseDAO;
	}

	/**
	 * @param baseDAO the baseDAO to set
	 */
	public void setBaseDAO(IBaseDAO baseDAO) {
		this.baseDAO = baseDAO;
	}
	/**
	 *获取统计信息
	 *@author songxiaohu
	 **/
	public int getmsgStateInfo(Map<String,Object>map) {

		StringBuffer strBuf =new StringBuffer();
		String beginTime=MapGetter.getString(map,"beginTime");
		String endTime=MapGetter.getString(map,"endTime");
		List<String>listtime=DateRegUtil.iterateTime(beginTime, endTime);
		String flag=MapGetter.getString(map,"flag");
		StringBuffer sbSql=new StringBuffer();
		int num=0;
		for(String time:listtime){
			time=time.replaceAll("-","");
			sbSql.append("select MsgESS_Vcl_ID,");
			//当天运行时长
			sbSql.append("case when MsgESS_iWork<=24 and MsgESS_iWork>=0 then MsgESS_iWork else 0 end as toRunTime,");
			//当天油耗
			sbSql.append("case when MsgESS_iOilCons>=0 and MsgESS_iOilCons<=1800 then MsgESS_iOilCons else 0 end as toRunOil,");
			//五种里程合并
			sbSql.append("case when MsgESS_iRunKm>=0 and MsgESS_iRunKm<=3000 then MsgESS_iRunKm else 0 end as MsgESS_iRunKm,");
			//当天ECM里程
			//sbSql.append("case when MsgESS_iRunKmECM>=0 and MsgESS_iRunKmECM<=3000 then MsgESS_iRunKmECM else 0 end as MsgESS_iRunKmECM,");
			//当天GPS里程
			//sbSql.append("case when MsgESS_iRunKmGPS>=0 and MsgESS_iRunKmGPS<=3000 then MsgESS_iRunKmGPS else 0 end as MsgESS_iRunKmGPS,");
			//当天仪表里程
			//sbSql.append("case when MsgESS_iRunKmExact>=0 and  MsgESS_iRunKmExact<=3000 then MsgESS_iRunKmExact else 0 end as MsgESS_iRunKmExact,");
			//当天ECM高精度里程
			//sbSql.append("case when MsgESS_iRunKmExact_ECM>=0 and MsgESS_iRunKmExact_ECM<=3000 then MsgESS_iRunKmExact_ECM else 0 end as MsgESS_iRunKmExact_ECM,");
			//当天ECU里程
			//sbSql.append("case when MsgESS_iRunKmECU>=0 and MsgESS_iRunKmECU<=3000 then MsgESS_iRunKmECU else 0 end   as MsgESS_iRunKmECU,");
			//当天尿素消耗
			sbSql.append("case when MsgESS_iUreaConsumption>=0 and MsgESS_iUreaConsumption<=200 then MsgESS_iUreaConsumption else 0 end as MsgESS_iUreaConsumption ,");
			//当天怠速油耗
			sbSql.append("case when MsgESS_iIdleWorkTime_PGN>=0 and MsgESS_iIdleWorkTime_PGN<=200 then MsgESS_iIdleWorkTime_PGN else 0 end as MsgESS_iIdleWorkTime_PGN,");
			//当天怠速时长
			sbSql.append("case when MsgESS_iIdleWorkTime_UDS>=0 and MsgESS_iIdleWorkTime_UDS<=24 then MsgESS_iIdleWorkTime_UDS else 0 end as MsgESS_iIdleWorkTime_UDS,");
			//急刹车次数
	    	sbSql.append("MsgESS_SlamBrakesNum AS JSCTimes, ");
	    	//超速行驶次数
	    	sbSql.append("MsgESS_OverSpeedNum_2 AS CSXSTimes, ");
	    	//疲劳驾驶
	    	sbSql.append("MsgESS_FatigueDrivingNum AS PLJSTimes, ");
	    	sbSql.append(" MsgESS_State,MsgESS_Complete ");
	    	sbSql.append(" from "+GlobalDBVar.getDBFullName_Conn("Msg_EquipmentState_Statistics_3rd_"+time, "3", null));
	    	sbSql.append(" inner join vehicleMiddle_IEMS on vehicleid=MsgESS_Vcl_ID");
	    	/*sbSql.append(" where MsgESS_State = 0 and( (subString(cast (MsgESS_Complete_1 as varchar),1,1) = 1 and charindex(cast(vehiclerumkmid as varchar)+'1',cast(MsgESS_Complete_2 as varchar)) >0)");
	    	sbSql.append(" or (subString(cast (MsgESS_Complete_1 as varchar),1,1) = 0 and charindex(cast(vehiclerumkmid as varchar)+'0',cast(MsgESS_Complete_2 as varchar)) >0))  union all ");*/
	    	sbSql.append("  where MsgESS_State = 0 and MsgESS_Complete=1 union all ");
		}
		System.out.println(sbSql.toString());
		if(sbSql.length()>0){
			String sql=sbSql.substring(0,sbSql.length()-10);
			strBuf.append("select MsgESS_Vcl_ID,");
			strBuf.append( "'"+beginTime+"',");
			strBuf.append( "'"+endTime+"',");
			strBuf.append(" sum(toRunTime),");
			strBuf.append(" sum(toRunOil),");
			strBuf.append(" sum(MsgESS_iRunkm ),");
//			strBuf.append(" sum(MsgESS_iRunKmECM),");
//			strBuf.append(" sum(MsgESS_iRunKmGPS),");
//			strBuf.append(" sum(MsgESS_iRunKmExact),");
//			strBuf.append(" sum(MsgESS_iRunKmExact_ECM),");
//			strBuf.append(" sum(MsgESS_iRunKmECU ),");
			strBuf.append(" sum(MsgESS_iIdleWorkTime_PGN),");
			strBuf.append(" sum(MsgESS_iIdleWorkTime_UDS),");
			strBuf.append(" sum(MsgESS_iUreaConsumption),");
			strBuf.append(" sum(CSXSTimes),");
			strBuf.append(" sum(PLJSTimes),");
			strBuf.append(" sum(JSCTimes),");
			strBuf.append(" sum(case when MsgESS_iRunkm>200 then 1 else 0 end)");
			strBuf.append(" from ("+sql+")mq group by MsgESS_Vcl_ID");
			String prefix="";
			String tableName="";
			if("1".equals(flag)){//近七天
				prefix="MsgESS";
				tableName="Msg_EquipmentState_Statistics_ThisWeek_N";
			}else{//上一个近七天
				prefix="MsgESSL";
				tableName="Msg_EquipmentState_Statistics_LastWeek_N";
			}
			String resultSql="truncate table "+tableName+";" +
					"insert into "+tableName+"(" +
							prefix+"_Vcl_ID,"+
							prefix+"_MsgBTime,"+
							prefix+"_MsgETime,"+
							prefix+"_iWork,"+
							prefix+"_iOilCons,"+
							prefix+"_irunKm,"+
//							prefix+"_iRunKmECM,"+
//							prefix+"_iRunKmGPS,"+
//							prefix+"_iRunKmExact,"+
//							prefix+"_iRunKmExact_ECM,"+
//							prefix+"_iRunKmECU,"+
							prefix+"_iIdleWorkTime_PGN,"+
							prefix+"_iIdleWorkTime_UDS,"+
							prefix+"_iUreaConsumption,"+
							prefix+"_OverSpeedNum_2,"+
							prefix+"_FatigueDrivingNum,"+
							prefix+"_SlamBrakesNum,"+
							prefix+"_workOn) "+strBuf;
			num=new MyConnectDSDAO().execInsert(resultSql, new Object[]{});
		}
		return num;
	}
	/**
	 * 构建中间表
	 */
	public void ProMiddleTable(){
		String sql = "Select count(id) as counts FROM SysObjects Where name = 'vehicleMiddle_IEMS' and xtype = 'U'";
		String resultSql="";
		if(Integer.parseInt(MapGetter.getString(this.getBaseDAO().execQueryOne(sql, new Object[]{}),"counts"))>0){
			 resultSql=" drop table vehicleMiddle_IEMS;SELECT * INTO vehicleMiddle_IEMS FROM V_VEHICLEINFO";
		}else{
			 resultSql=" SELECT * INTO vehicleMiddle_IEMS FROM V_VEHICLEINFO";
		}
		this.baseDAO.execInsert(resultSql, new Object[]{});
	}
}
